package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.dao.BaseDao;
import com.sq.partner.manager.dao.IGatewayUserDao;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.GatewayRole;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.GatewayUserRole;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class GatewayUserDaoImpl extends BaseDaoImpl implements IGatewayUserDao {
	private static Logger logger = LogUtil.getLog(BaseDaoImpl.class);
	
	/**
	 * 查询所有用户列表，可分页，如果有查询条件，则拼接查询条件进行查询
	 */
	@Override
	public Page queryPage(Page page,Integer pageIndex,Map<String,String> param, GatewayUser user) throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		List<String> list=new ArrayList<String>();
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			String sql;
			
			if((user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE))){//有总公司机构
				sqlbuf.append("select a.userid,a.user_code,a.user_name,a.telephone,a.email,decode(a.status,1,'有效','无效') as status from t_gateway_user a where 1=1");
			}else {
				sqlbuf.append("select a.userid,a.user_code,a.user_name,a.telephone,a.email,a.status from (select u.userid,u.user_code,u.user_name,u.telephone,u.email,decode(u.status,1,'有效','无效') as status,u.oper_time from t_gateway_user u ,")
				.append(" (select userid from (select d.dept_code d1, u.userid  from t_gateway_user  u, t_gateway_user_role r,t_gateway_role_dept d ")
				.append(" where u.userid = r.userid and r.roleid = d.role_id) t left join  ")
				.append(" (SELECT DISTINCT TT.DEPT_CODE d2 FROM T_CORE_DEPT TT START WITH TT.DEPT_CODE IN ("+user.getDepartmentStr()+")  CONNECT BY NOCYCLE TT.SNR_DEPT = PRIOR TT.DEPT_CODE) d ")
				.append("  on t.d1 = d.d2 group by t.userid having count(t.d1) = count(d.d2)) u2 ")
				.append("  where u.userid = u2.userid ")
				.append("  union  ")
				.append(" select u.userid,u.user_code,u.user_name,u.telephone,u.email,decode(u.status,1,'有效','无效') as status,u.oper_time from t_gateway_user u where u.oper_user ='"+user.getUserid()+"' ")
				.append(" ) a where 1=1 ");
			}
			if(null!=param.get("userCode")&&StrUtil.isNotEmpty(param.get("userCode"))){
				sqlbuf.append(" and a.user_code like ? ");
				list.add("%"+param.get("userCode")+"%");
			}
			if(null!=param.get("userName")&&StrUtil.isNotEmpty(param.get("userName"))){
				sqlbuf.append(" and a.user_name like ? ");
				list.add("%"+param.get("userName")+"%");
			}
			sqlbuf.append(" order by a.oper_time desc");
			sql=sqlbuf.toString();
			
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				pstmt.setObject(i+1, list.get(i));
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			Page p = new Page(page.getCurrent(),total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,p).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				pstmt1.setObject(i+1, list.get(i));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			p.setData(lists);
			return p;
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		
	}
	
	/**
	 * 根据用户ID删除该用户
	 * @param id
	 */
	@Override
	public void delUserById(GatewayUser user) throws Exception {
		this.delete("delGatewayUserById", user);
		
	}

	/**
	 * 根据用户id查询用户信息
	 */
	@Override
	public GatewayUser queryUserById(Long userid) throws Exception {
		return (GatewayUser)this.query("queryGatewayUserById", userid);
	}

	/**
	 * 修改保存用户信息
	 */
	@Override
	public boolean saveUser(GatewayUser user) throws Exception {
		return this.add("updateGatewayUserNotNull", user);
	}

	@Override
	public boolean modifyPwd(GatewayUser user) throws Exception {
		return this.add("updateGatewayUserNotNull", user);
	}

	@Override
	public List<GatewayRole> getUserRole(Long userid) throws Exception {
		Map<String,Long> map = new HashMap<String,Long>();
		map.put("userid", userid);
		List<GatewayRole> list = this.queryList("queryGatewayUserRoleByMap", map);
		return list;
	}

	@Override
	public boolean addUser(GatewayUser user) throws Exception {
		return this.add("insertGatewayUser", user);
	}

	@Override
	public GatewayUser checkLogin(Map<String,String> map) throws Exception {
		return (GatewayUser)this.query("queryGatewayUserByMap", map);
	}
	
	@Override
	public GatewayUser queryUserCountByMap(Map<String,String> map) throws Exception {
		return (GatewayUser)this.query("queryGatewayUserByMap", map);
	}
	
	@Override
	public GatewayUser queryUser(String userCode) throws Exception {
		return (GatewayUser)this.query("queryGatewayUserByUserCode",userCode);
	}

	@Override
	public List<GatewayRole> queryAllRoles(Map<String,String> map) throws Exception {
		return this.queryList("queryGatewayRoleByMap", map);
	}

	@Override
	public List<GatewayRole> queryAllRolesByUser(GatewayUser user)
			throws Exception {
		Map<String,String> map = new HashMap<String, String>();
		
		if((user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE))){
			return this.queryList("queryGatewayRoleByMap", map);
		}else{
			map.put("DepartmentStr", user.getDepartmentStr());
			return this.queryList("queryGatewayRoleByUser", map);
		}
	}
	@Override
	public Page queryRolesPageByUser(Page page,Integer pageIndex,Map<String,String> param,GatewayUser user)throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		List<String> list=new ArrayList<String>();
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			String sql;
			sqlbuf.append(" select u.roleid id,r.roleid,r.role_name,l.dept_cname,rd.dept_code,r.oper_time from t_gateway_role r ")
			.append("  left join t_gateway_role_dept rd on rd.role_id = r.roleid ")
			.append(" left join t_core_dept l on rd.dept_code = l.dept_code ");
			
			if(null!=param.get("userId")&&StrUtil.isNotEmpty(param.get("userId"))){
				sqlbuf.append("left join t_gateway_user_role u on u.roleid=r.roleid and u.userid="+param.get("userId"));
			}
			if((user.getDepartmentStr().contains(Constants.HQ_BRANCH_CODE))){//总公司机构
				sqlbuf.append(" where 1=1 ");
			}else{
				sqlbuf.append(" where rd.dept_code in (")
				.append(" select distinct tt.dept_code  from t_core_dept tt start with tt.dept_code in ("+user.getDepartmentStr()+") ")
				.append(" connect by nocycle   tt.snr_dept = prior tt.dept_code ) ");
			}
			
			sql = "select * from ( "+sqlbuf.toString()+" ) a where 1=1 ";
			
			if(null!=param.get("roleName")&&StrUtil.isNotEmpty(param.get("roleName"))){
				sql = sql +" and a.role_name like ? ";
				list.add("%"+param.get("roleName")+"%");
			}
			if(null!=param.get("deptCode")&&StrUtil.isNotEmpty(param.get("deptCode"))){
				sql = sql +" and a.dept_code in ("
				+" select distinct tt.dept_code  from t_core_dept tt start with tt.dept_code in (?) "
				+" connect by nocycle   tt.snr_dept = prior tt.dept_code ) ";
				list.add(param.get("deptCode"));
			}
			sql=sql +" order by id,a.oper_time desc";
			
			logger.debug("执行SQL：" + sql);


			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				pstmt.setObject(i+1, list.get(i));
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);

			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				pstmt1.setObject(i+1, list.get(i));
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
			return page;
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
	}
	@Override
	public boolean setRoleToUser(GatewayUserRole role) throws Exception {
		return this.add("insertGatewayUserRole", role);
	}

	@Override
	public boolean removeRolesFromUser(Long userId) throws Exception {
		return this.delete("delUserRoleUserid", userId);
	}

	@Override
	public void delUserRoleUserid(Long   userid) throws Exception {
		   this.delete("delUserRoleUserid", userid);
	}

	@Override
	public List<GatewayUserRole> getUserCountOfOneRole(Map<String, Long> map)
			throws Exception {
		return this.queryList("queryGatewayUserRoleByMap", map);
	}

	@Override
	public List<CoreDept> queryDeptCode(GatewayUser user) throws Exception {
		String deptStr = user.getDepartmentStr();
		return this.queryList("queryDeptCode", deptStr);
	}

	
}
